package MySQLConnection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;

import model.Solution;

import common.Exceptions.DAOException;

import DAO.SolutionDAO;

public class MySQLSolutionDAO implements SolutionDAO
{
	private final static String TABLE_NAME = MySqlDAOFactory.DBName + ".solution"; 
	private static MySQLSolutionDAO instance;
	
	public static MySQLSolutionDAO getInstance()
	{
		if(instance == null)
			instance = new MySQLSolutionDAO();
		
		return instance;
	}
	
	@Override
	public void insert(Solution item) throws DAOException 
	{
		String query = "INSERT INTO " + TABLE_NAME + "(sol_id, sol_name, sol_description, sol_assumptions, sol_outOfScope) VALUES(?, ?, ?, ?, ?)";

		try 
		{
			PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(query) ;
			preStatement.setString(1, item.getID().toString());
			preStatement.setString(2, item.getName());
			preStatement.setString(3, item.getDescription());
			preStatement.setString(4, item.getAssumptions());
			preStatement.setString(5, item.getOutOfScope());
			
			preStatement.executeUpdate();
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The insert method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public void update(Solution item) throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE sol_id = '" + item.getID().toString() + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String updateQuery = "UPDATE " + TABLE_NAME + " SET " +
						"sol_name = ?, sol_Description = ?, sol_assumptions = ?, sol_outOfScope = ? WHERE sol_id = ?";
				
				PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(updateQuery);
				preStatement.setString(1, item.getName());
				preStatement.setString(2, item.getDescription());
				preStatement.setString(3, item.getAssumptions());
				preStatement.setString(4, item.getOutOfScope());
				preStatement.setString(5, item.getID().toString());
				preStatement.executeUpdate();
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Sol_id = " + item.getID());
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + item.getID() + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The insert method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
		
	}

	@Override
	public void delete(UUID id) throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE sol_id = '" + id + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String deleteQuery = "DELETE FROM " + TABLE_NAME + " WHERE sol_id = '" + id + "'";
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				if(statement.executeUpdate(deleteQuery) == 0)
					throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " couldn't be deleted due to an unknown error.");
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Sol_id = " + id);
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The delete method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public List<Solution> getAll() throws DAOException
	{
		String query = "SELECT * FROM " + TABLE_NAME + ";";
		List<Solution> solutions = new LinkedList<Solution>();

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			while(dataSet.next())
			{
				UUID solID = UUID.fromString(dataSet.getString("sol_id"));
				String solName = dataSet.getString("sol_name");
				String solDescription = dataSet.getString("sol_description");
				String solAssumptions = dataSet.getString("sol_assumptions");
				String solOutOfScope = dataSet.getString("sol_outOfScope");
				
				Solution sol = new Solution(solID);
				sol.setName(solName);
				sol.setAssumptions(solAssumptions);
				sol.setDescription(solDescription);
				sol.setOutOfScope(solOutOfScope);
				
				solutions.add(sol);
			}
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
		
		return solutions;
	}

	@Override
	public Solution getByID(UUID id) throws DAOException 
	{
		Solution sol = null;
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE sol_id = '" + id.toString() + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String solName = dataSet.getString("sol_name");
				String solDescription = dataSet.getString("sol_description");
				String solAssumptions = dataSet.getString("sol_assumptions");
				String solOutOfScope = dataSet.getString("sol_outOfScope");
				
				sol = new Solution(id);
				sol.setName(solName);
				sol.setAssumptions(solAssumptions);
				sol.setDescription(solDescription);
				sol.setOutOfScope(solOutOfScope);
				
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Sol_id = " + id);
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getByID method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
		
		return sol;
	}
}
